/*
 * @copyright 2010 Gerald Jacobson
 * @license GNU General Public License
 * 
 * This file is part of MyTricTrac.
 *
 * MyTricTrac is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * MyTricTrac is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with My Accounts.  If not, see <http://www.gnu.org/licenses/>.
 */
package org.amphiprion.montrictracv4.db.dao;

import java.util.ArrayList;
import java.util.List;

import org.amphiprion.montrictracv4.App;
import org.amphiprion.montrictracv4.ApplicationConstants.DATA_TYPE;
import org.amphiprion.montrictracv4.db.model.Entity.DbState;
import org.amphiprion.montrictracv4.db.model.Game;
import org.amphiprion.montrictracv4.db.model.Search;
import org.amphiprion.montrictracv4.db.model.UserGameData;
import org.amphiprion.montrictracv4.db.model.UserSearchData;
import org.amphiprion.montrictracv4.web.util.PreferenceUtil;

import android.content.Context;
import android.database.Cursor;

/**
 * This class is responsible of all database collection access.
 * 
 * @author amphiprion
 * 
 */
public class GameDao extends AbstractDao {
	/** The singleton. */
	private static GameDao instance;
	private static String[] idUserCaracs = { "28d08f814e7cf01fbe93902aa3e930bade", "28d08f814e7cf01fbe93902aa3e930b7de" };

	/**
	 * Hidden constructor.
	 * 
	 * @param context
	 *            the application context
	 */
	private GameDao(Context context) {
		super(context);
	}

	/**
	 * Return the singleton.
	 * 
	 * @param context
	 *            the application context
	 * @return the singleton
	 */
	public static GameDao getInstance(Context context) {
		if (instance == null) {
			instance = new GameDao(context);
		}
		return instance;
	}

	public Game getGameSumary(String gameId) {
		String sql = "SELECT id, min_player, max_player from GAME where id=?";
		Cursor cursor = getDatabase().rawQuery(sql, new String[] { gameId });
		if (cursor.moveToFirst()) {
			Game entity = new Game(cursor.getString(0));
			entity.minPlayer = cursor.getInt(1);
			entity.maxPlayer = cursor.getInt(2);
			cursor.close();

			sql = "SELECT id_parent_game FROM EXTENSION_OF where id_game=?";
			cursor = getDatabase().rawQuery(sql, new String[] { gameId });
			if (cursor.moveToFirst()) {
				do {
					entity.extensionOf.add(cursor.getString(0));
				} while (cursor.moveToNext());
			}
			cursor.close();
			return entity;
		} else {
			cursor.close();
			return null;
		}

	}

	public List<Game> getGames(String ludothequeId, boolean showExtension, String nameFilter, boolean orderByDate) {

		String sqlSelect = "SELECT distinct g.id, g.name, g.image,g.score, g.nb_votes, g.isExtension, g.age_min, g.min_player, g.max_player, lg.min_player, lg.max_player, g.duration";
		String sqlFrom = " from LUDOTHEQUE_GAME lg, GAME g";
		String sqlWhere = " where lg.id_ludo=? and lg.id_game=g.id";
		if (nameFilter != null && !"".equals(nameFilter.trim())) {
			sqlWhere += " and g.name COLLATE NOCASE like ?";
		}
		int index = 12;
		List<Integer> userDataForDisplayIndexes = new ArrayList<Integer>();

		String idCarac1 = PreferenceUtil.pref.getString("USER_GAME_DATA_1_ID", "");
		if (!"".equals(idCarac1)) {
			String tableCarac = PreferenceUtil.pref.getString("USER_GAME_DATA_1_TABLE", "");
			String tableCaracJoin = PreferenceUtil.pref.getString("USER_GAME_DATA_1_TABLE_JOIN", "");
			String tableCaracJoinFK = PreferenceUtil.pref.getString("USER_GAME_DATA_1_TABLE_JOIN_FK", "");
			sqlSelect += ",uc" + index + ".id";
			sqlSelect += ",uc" + index + ".name";
			sqlSelect += ",uc" + index + ".data_type";
			sqlSelect += ",ucg" + index + ".value";
			sqlSelect += ",'" + tableCarac + "'";

			sqlFrom += ",(select uc" + index + ".id,uc" + index + ".name,uc" + index + ".data_type from " + tableCarac + " uc" + index + " where uc" + index + ".id='" + idCarac1 + "') uc" + index;

			sqlFrom += " Left outer join " + tableCaracJoin + " ucg" + index;
			sqlFrom += " on g.id=ucg" + index + ".id_game and ucg" + index + "." + tableCaracJoinFK + "='" + idCarac1 + "'";
			userDataForDisplayIndexes.add(index);
			index += 5;
		}

		String idCarac2 = PreferenceUtil.pref.getString("USER_GAME_DATA_2_ID", "");
		if (!"".equals(idCarac2)) {
			String tableCarac = PreferenceUtil.pref.getString("USER_GAME_DATA_2_TABLE", "");
			String tableCaracJoin = PreferenceUtil.pref.getString("USER_GAME_DATA_2_TABLE_JOIN", "");
			String tableCaracJoinFK = PreferenceUtil.pref.getString("USER_GAME_DATA_2_TABLE_JOIN_FK", "");
			sqlSelect += ",uc" + index + ".id";
			sqlSelect += ",uc" + index + ".name";
			sqlSelect += ",uc" + index + ".data_type";
			sqlSelect += ",ucg" + index + ".value";
			sqlSelect += ",'" + tableCarac + "'";

			sqlFrom += ",(select uc" + index + ".id,uc" + index + ".name,uc" + index + ".data_type from " + tableCarac + " uc" + index + " where uc" + index + ".id='" + idCarac2 + "') uc" + index;

			sqlFrom += " Left outer join " + tableCaracJoin + " ucg" + index;
			sqlFrom += " on g.id=ucg" + index + ".id_game and ucg" + index + "." + tableCaracJoinFK + "='" + idCarac2 + "'";
			userDataForDisplayIndexes.add(index);
			index += 5;
		}

		if (!showExtension) {
			sqlWhere += " and g.isExtension=0";
		}

		if (App.currentSearch != null) {
			Search search = App.currentSearch;
			if (search.age > 0) {
				sqlWhere += " and (g.age_min<=" + search.age + " or g.age_min=0)";
			}
			if (search.minDuration > 0) {
				sqlWhere += " and (g.duration>=" + search.minDuration + " or g.duration=0)";
			}
			if (search.maxDuration > 0) {
				sqlWhere += " and (g.duration<=" + search.maxDuration + " or g.duration=0)";
			}

			if (search.minPlayer > 0) {
				if (search.exact) {
					sqlWhere += " and (g.min_player=" + search.minPlayer + " or g.min_player=0) and (g.max_player>=" + search.minPlayer + " or g.max_player=0)";
				} else {
					sqlWhere += " and (g.min_player<=" + search.minPlayer + " or g.min_player=0) and (g.max_player>=" + search.minPlayer + " or g.max_player=0)";
				}
			}
			if (search.maxPlayer > 0) {
				if (search.exact) {
					sqlWhere += " and (g.max_player=" + search.maxPlayer + " or g.max_player=0) and (g.min_player<=" + search.maxPlayer + " or g.min_player=0)";
				} else {
					sqlWhere += " and (g.max_player>=" + search.maxPlayer + " or g.max_player=0) and (g.min_player<=" + search.maxPlayer + " or g.min_player=0)";
				}
			}

			// User Search info
			for (UserSearchData usd : search._userSearchInfos) {
				// sqlSelect += ",uc" + index + ".id";
				// sqlSelect += ",uc" + index + ".name";
				// sqlSelect += ",uc" + index + ".data_type";
				// sqlSelect += ",ucg" + index + ".value";

				sqlFrom += ",(select uc" + index + ".id,uc" + index + ".name,uc" + index + ".data_type from USER_INFO uc" + index + " where uc" + index + ".id='" + idCarac1 + "') uc" + index;

				sqlFrom += " Left outer join USER_INFO_GAME ucg" + index;
				sqlFrom += " on g.id=ucg" + index + ".id_game and ucg" + index + ".id_user_info='" + usd.dataId + "'";

				sqlWhere += " and (";
				if (search.includeGameWithMissingInfo) {
					sqlWhere += "ucg" + index + ".value is null or ";
				}
				if (usd.typeForData == DATA_TYPE.FLOAT) {
					if (usd.min_value > 0 && usd.max_value > 0) {
						sqlWhere += "(ROUND(CAST(ucg" + index + ".value as INTEGER),0) >= " + usd.min_value + " and ROUND(CAST(ucg" + index + ".value as INTEGER),0) <= " + usd.max_value + ")";
					} else if (usd.min_value > 0) {
						sqlWhere += "ROUND(CAST(ucg" + index + ".value as INTEGER),0) >= " + usd.min_value;
					} else {
						sqlWhere += "ROUND(CAST(ucg" + index + ".value as INTEGER),0) <= " + usd.max_value;
					}
				} else {
					// TODO search type STRING
					sqlWhere += "1=1";
				}
				sqlWhere += ")";
				index += 4;
			}

			// User Search carac
			for (UserSearchData usd : search._userSearchCaracs) {
				// sqlSelect += ",uc" + index + ".id";
				// sqlSelect += ",uc" + index + ".name";
				// sqlSelect += ",uc" + index + ".data_type";
				// sqlSelect += ",ucg" + index + ".value";

				sqlFrom += ",(select uc" + index + ".id,uc" + index + ".name,uc" + index + ".data_type from USER_CARAC uc" + index + " where uc" + index + ".id='" + idCarac1 + "') uc" + index;

				sqlFrom += " Left outer join USER_CARAC_GAME ucg" + index;
				sqlFrom += " on g.id=ucg" + index + ".id_game and ucg" + index + ".id_user_carac='" + usd.dataId + "'";

				sqlWhere += " and (";
				if (search.includeGameWithMissingCarac) {
					sqlWhere += "ucg" + index + ".value is null or ";
				}
				if (usd.typeForData == DATA_TYPE.FLOAT) {
					if (usd.min_value > 0 && usd.max_value > 0) {
						sqlWhere += "(ROUND(CAST(ucg" + index + ".value as INTEGER),0) >= " + usd.min_value + " and ROUND(CAST(ucg" + index + ".value as INTEGER),0) <= " + usd.max_value + ")";
					} else if (usd.min_value > 0) {
						sqlWhere += "ROUND(CAST(ucg" + index + ".value as INTEGER),0) >= " + usd.min_value;
					} else {
						sqlWhere += "ROUND(CAST(ucg" + index + ".value as INTEGER),0) <= " + usd.max_value;
					}
				} else {
					// TODO search type STRING
					sqlWhere += "1=1";
				}
				sqlWhere += ")";
				index += 4;
			}

			// User Search mecanism
			for (UserSearchData usd : search._userSearchMecanisms) {
				// sqlSelect += ",uc" + index + ".id";
				// sqlSelect += ",uc" + index + ".name";
				// sqlSelect += ",uc" + index + ".data_type";
				// sqlSelect += ",ucg" + index + ".value";

				sqlFrom += ",(select uc" + index + ".id,uc" + index + ".name,uc" + index + ".data_type from USER_MECANISM uc" + index + " where uc" + index + ".id='" + idCarac1 + "') uc" + index;

				sqlFrom += " Left outer join USER_MECANISM_GAME ucg" + index;
				sqlFrom += " on g.id=ucg" + index + ".id_game and ucg" + index + ".id_user_mecanism='" + usd.dataId + "'";

				sqlWhere += " and (";
				if (search.includeGameWithMissingMecanism) {
					sqlWhere += "ucg" + index + ".value is null or ";
				}
				if (usd.typeForData == DATA_TYPE.FLOAT) {
					if (usd.min_value > 0 && usd.max_value > 0) {
						sqlWhere += "(ROUND(CAST(ucg" + index + ".value as INTEGER),0) >= " + usd.min_value + " and ROUND(CAST(ucg" + index + ".value as INTEGER),0) <= " + usd.max_value + ")";
					} else if (usd.min_value > 0) {
						sqlWhere += "ROUND(CAST(ucg" + index + ".value as INTEGER),0) >= " + usd.min_value;
					} else {
						sqlWhere += "ROUND(CAST(ucg" + index + ".value as INTEGER),0) <= " + usd.max_value;
					}
				} else {
					// TODO search type STRING
					sqlWhere += "1=1";
				}
				sqlWhere += ")";
				index += 4;
			}

		}

		String sql = sqlSelect + sqlFrom + sqlWhere;

		if (orderByDate) {
			// no order (the database insert order is already the good order) -> order of trictrac ludo content
		} else {
			sql += " order by g.name COLLATE NOCASE";
		}

		String[] params;
		if (nameFilter != null && !"".equals(nameFilter.trim())) {
			params = new String[] { ludothequeId, "%" + nameFilter.toLowerCase() + "%" };
		} else {
			params = new String[] { ludothequeId };
		}

		Cursor cursor = getDatabase().rawQuery(sql, params);
		ArrayList<Game> result = new ArrayList<Game>();
		if (cursor.moveToFirst()) {
			do {
				Game entity = new Game(cursor.getString(0));
				entity.name = cursor.getString(1);
				entity.image = cursor.getString(2);
				entity.score = cursor.getFloat(3);
				entity.nbVotes = cursor.getInt(4);
				entity.extension = cursor.getInt(5) != 0;
				entity.ageMin = cursor.getInt(6);
				entity.minPlayer = cursor.getInt(7);
				entity.maxPlayer = cursor.getInt(8);
				entity.c_minPlayer = cursor.getInt(9);
				entity.c_maxPlayer = cursor.getInt(10);
				entity.duration = cursor.getInt(11);

				if (userDataForDisplayIndexes.size() > 0) {
					List<UserGameData> forDisplay = new ArrayList<UserGameData>();
					entity._ugcForDisplay = forDisplay;
					for (int indexInSelect : userDataForDisplayIndexes) {
						UserGameData ugd = new UserGameData(entity.getId(), cursor.getString(indexInSelect));
						ugd.nameFromData = cursor.getString(indexInSelect + 1);
						String uctype = cursor.getString(indexInSelect + 2);
						if (uctype != null) {
							ugd.typeForData = DATA_TYPE.values()[Integer.parseInt(uctype)];
						}
						ugd.value = cursor.getString(indexInSelect + 3);
						if (ugd.typeForData != null && ugd.value != null) {
							entity._ugcForDisplay.add(ugd);
						}
						ugd._table = cursor.getString(indexInSelect + 4);
					}
				}
				result.add(entity);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return result;
	}

	/**
	 * Persist a new game.
	 * 
	 * @param game
	 *            the new game
	 */
	public void create(Game game) {
		String sql = "insert into GAME (id,name,image,score, nb_votes, isExtension, age_min, min_player, max_player, duration) values (?,?,?,?,?,?,?,?,?,?)";
		Object[] params = new Object[10];
		params[0] = game.getId();
		params[1] = game.name;
		params[2] = game.image;
		params[3] = game.score;
		params[4] = game.nbVotes;
		params[5] = game.extension ? 1 : 0;
		params[6] = game.ageMin;
		params[7] = game.minPlayer;
		params[8] = game.maxPlayer;
		params[9] = game.duration;

		execSQL(sql, params);

		updateJoinTables(game);
	}

	/**
	 * Update an existing game.
	 * 
	 * @param game
	 *            the game to update
	 */
	public void update(Game game) {
		String sql = "update GAME set name=?,image=?,score=?,nb_votes=?,isExtension=?, age_min=?, min_player=?,max_player=?,duration=? WHERE id=?";
		Object[] params = new Object[10];
		params[0] = game.name;
		params[1] = game.image;
		params[2] = game.score;
		params[3] = game.nbVotes;
		params[4] = game.extension ? 1 : 0;
		params[5] = game.ageMin;
		params[6] = game.minPlayer;
		params[7] = game.maxPlayer;
		params[8] = game.duration;
		params[9] = game.getId();

		execSQL(sql, params);

		updateJoinTables(game);
	}

	private void updateJoinTables(Game game) {
		String sql = "delete from USER_INFO_GAME where " + "id_game=?";
		execSQL(sql, new String[] { game.getId() });

		sql = "delete from USER_CARAC_GAME where " + "id_game=?";
		execSQL(sql, new String[] { game.getId() });

		sql = "delete from USER_MECANISM_GAME where " + "id_game=?";
		execSQL(sql, new String[] { game.getId() });

		sql = "delete from EXTENSION_OF where " + "id_game=?";
		execSQL(sql, new String[] { game.getId() });

		for (UserGameData info : game.userInfos) {
			UserInfoDao.getInstance(context).persit(info);
		}
		for (UserGameData info : game.userCarac) {
			UserCaracDao.getInstance(context).persit(info);
		}
		for (UserGameData info : game.userMecanism) {
			UserMecanismDao.getInstance(context).persit(info);
		}

		for (String baseGameId : game.extensionOf) {
			sql = "insert into EXTENSION_OF (id_game, id_parent_game) values (?,?)";
			Object[] params = new Object[2];
			params[0] = game.getId();
			params[1] = baseGameId;

			execSQL(sql, params);
		}
	}

	public void persist(Game game) {
		if (game.getState() == DbState.NEW) {
			create(game);
		} else if (game.getState() == DbState.LOADED) {
			update(game);
		}
	}

	public boolean isInDb(String gameId) {
		boolean result = false;
		String sql = "SELECT count(*) from GAME where id=?";
		Cursor cursor = getDatabase().rawQuery(sql, new String[] { gameId });
		cursor.moveToFirst();
		result = cursor.getInt(0) != 0;
		cursor.close();
		return result;
	}
}
